package com.deng.common.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.common.dao.IOperateDepartment;
import com.deng.common.vo.HisOrganizationVo;
import com.deng.common.vo.OperateDepartmentVo;
import com.deng.common.vo.OrganizationVo;
import com.deng.util.FunctionFilter;

public class OperateDepartmentImpl extends SQLHelper implements IOperateDepartment {

	public List<OperateDepartmentVo> addAndGetOperateDepartmentList(OperateDepartmentVo vo) {
		try{
			Connection con = getConnect();
			ResultSet rs = queryData(con,"select case when max(id)+1 is null then 1 else max(id)+1 end as id" +
					" from t_sys_stat_operate_department");	
			int maxID = 0;
			if(rs.next()){
				maxID = rs.getInt("id");
			}
			
			String sql ="insert into t_sys_stat_operate_department(id,name,code)" +
					"values(?,?,?)";
			try{
				PreparedStatement pst=con.prepareStatement(sql);
				pst.setInt(1, maxID);
				pst.setString(2, vo.name);
				pst.setString(3, vo.code);
				pst.executeUpdate();
				pst.close();
				vo.id = maxID;
				addOrUpdateOperateDepartmentOriginalCode(vo);
//				addOrUpdateOperateDepartmentHisCode(vo);
				return getOperateDepartmentListByCode(null);
			}catch(Exception ex){
				ex.printStackTrace();
				freeConnection(con);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<OperateDepartmentVo> deleteAndGetOperateDepartmentList(OperateDepartmentVo vo) {
		try{
			Connection con = getConnect();
						
			String sql ="delete t_sys_stat_operate_department where id = "+vo.id;
			try{
				PreparedStatement pst=con.prepareStatement(sql);
				pst.executeUpdate();
				pst.close();
				
				return getOperateDepartmentListByCode(null);
			}catch(Exception ex){
				ex.printStackTrace();
				freeConnection(con);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	public List<OperateDepartmentVo> updateAndGetOperateDepartmentList(
			OperateDepartmentVo vo) {
		try{
			Connection con = getConnect();
						
			String sql ="update t_sys_stat_operate_department set name = ? ,code = ? where id = "+vo.id;
			try{
				PreparedStatement pst=con.prepareStatement(sql);
				pst.setString(1, vo.name);
				pst.setString(2, vo.code);
				pst.executeUpdate();
				pst.close();
				addOrUpdateOperateDepartmentOriginalCode(vo);
//				addOrUpdateOperateDepartmentHisCode(vo);
				return getOperateDepartmentListByCode(null);
			}catch(Exception ex){
				ex.printStackTrace();
				freeConnection(con);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	public List<OperateDepartmentVo> getOperateDepartmentListByCode(String code) {
		try{
			Connection conn = getConnect();
			
			String sql ;
			if(code == null){
				sql = "select * from t_sys_stat_operate_department order by name";
			}else{
				sql = "select * from t_sys_stat_operate_department where code = '"+code+"' order by name";
			}
			ResultSet rs = queryData(conn,sql);	
			List<OperateDepartmentVo> list = new ArrayList<OperateDepartmentVo>();
			while(rs.next()){
				OperateDepartmentVo vo = new OperateDepartmentVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				vo.code = rs.getString("code");
				vo.formula = rs.getString("formula");
				list.add(vo);
			}
			freeConnection(conn);
			return list;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public void addOrUpdateOperateDepartmentOriginalCode(OperateDepartmentVo vo) {
		try{
			String s[] =  vo.original_code.split(",");			
			try{
				Connection conn = getConnect();
				
				String sql = "delete t_sys_stat_operate_department_vs_original_department where operate_id = "+vo.id;
				updateData(conn, sql);
				for(int i = 0;i< s.length;i++){
					sql = "insert into t_sys_stat_operate_department_vs_original_department " +
							"	(operate_id,original_id) " +
							"	values ("+vo.id+",'"+s[i]+"')";
					updateData(conn, sql);
				}
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public String getOperateDepartmentOriginalCode(OperateDepartmentVo vo) {
		try{
			Connection conn = getConnect();
			
			String sql = "select * from " +
					"	t_sys_stat_operate_department_vs_original_department where operate_id = "+vo.id;
			ResultSet rs = queryData(conn,sql);	
			String s = "";
			while(rs.next()){
				s += rs.getString("original_id")+",";
			}
			freeConnection(conn);
			if(s.length() > 0){
				return s.substring(0, s.length()-1);
			}
			return s;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return "";
	}

	public List<OrganizationVo> getOperateDepartmentOrganizationCode(OperateDepartmentVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "exec dbo.SP_GetFullNameOrganization 1";
			ResultSet rs = queryData(conn,sql);	
			List<OrganizationVo> s = new ArrayList<OrganizationVo>();
			OrganizationVo hovo ;
			while(rs.next()){
				hovo = new OrganizationVo();
				hovo.name = rs.getString("full_name");
				hovo.id = rs.getInt("id");
				hovo.type = "organization";
				hovo.operate_id = vo.id;
				String sql2 = "select * from t_sys_stat_operate_department_vs_organization" +
						"	where operate_id = " +vo.id+" and organization_id = "+hovo.id;
				ResultSet rs2 = queryData(conn,sql2);	
				if(rs2.next()){					
					hovo.selected = 1;					
				}else{
					hovo.selected = 0;
				}
				s.add(hovo);
			}
			freeConnection(conn);
			return s;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public OperateDepartmentVo updateOperateFunction(OperateDepartmentVo vo) {
		try{
			Connection conn = getConnect();
			int i = updateData(conn,"update t_sys_stat_operate_department " +
					"	set formula = '"+vo.formula+"'" +
					"	where id = "+vo.id);
			freeConnection(conn);
			if(i == 1){				
				return getOperateDepartmentById(vo.id);
			}else{
				return null;
			}			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public OperateDepartmentVo getOperateDepartmentById(Integer id) {
		try{
			Connection conn = getConnect();			
			if(id == null){
				return null;
			}
			
			String sql = "select * from t_sys_stat_operate_department where id = "+id;
			
			ResultSet rs = queryData(conn,sql);	
			if(rs.next()){
				OperateDepartmentVo vo = new OperateDepartmentVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				vo.code = rs.getString("code");
				vo.formula = rs.getString("formula");
				freeConnection(conn);
				return vo;
			}			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}

	public void addOrganizationCode(OrganizationVo vo) {
		try{
			try{
				Connection conn = getConnect();
				String sql = "insert into t_sys_stat_operate_department_vs_organization (operate_id,organization_id)" +
						"	values("+vo.operate_id+","+vo.id+")";
				updateData(conn, sql);
				freeConnection(conn);
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public void deleteOrganizationCode(OrganizationVo vo) {
		try{
			Connection conn = getConnect();			
			String sql = "delete t_sys_stat_operate_department_vs_organization where operate_id = "+vo.operate_id+
						"	and organization_id = "+vo.id+"";
			updateData(conn, sql);
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	

}
